import tkinter as tk
from tkinter import ttk
from db import db
from tkinter import messagebox
import time


class AboutFrame(tk.Frame):
    
    def __init__(self, root):
        """关于"""
        super().__init__(root)
        tk.Label(self, text='AboutTools：本作品由tkinter工具制作').grid(row=2,column=2,sticky='W')
        tk.Label(self, text='AboutAuthor：逆羽').grid(row=3,column=2,sticky='W')
        tk.Label(self, text='Copyright：逆羽').grid(row=4,column=2,sticky='W')

class ChangeFrame(tk.Frame):
    """修改密码"""
    def __init__(self, root):
        super().__init__(root)
        tk.Label(self, text='修改密码',font=("黑体",16)).pack()
        tk.Label(self, text='该功能暂时未开放，敬请期待！').pack()


class InsertFrame(tk.Frame):
    """库存"""
    def __init__(self, root):
        super(InsertFrame, self).__init__(root)
        tk.Label(self, text='U盘库存收录',font=("黑体",16)).grid(row=0,column=1,columnspan=2)
        self.u_type = tk.StringVar()
        self.u_number = tk.StringVar()
        self.u_status = tk.StringVar()
        self.create_page()

    def create_page(self):
        tk.Label(self, text='U盘类型').grid(row=1, column=1, pady=10,sticky='W')
        tk.Entry(self, textvariable=self.u_type).grid(row=1, column=2, pady=10,sticky='E')
        tk.Label(self, text='U盘编号').grid(row=2, column=1, pady=10,sticky='W')
        tk.Entry(self, textvariable=self.u_number).grid(row=2, column=2, pady=10,sticky='E')
        tk.Label(self, text='U盘状态').grid(row=3, column=1, pady=10,sticky='W')
        combobox = tk.ttk.Combobox(self, textvariable=self.u_status,width=15,height=2,font=("黑体",10),state="readonly")
        combobox['value'] = ('好', '坏')
        combobox.current(0)
        combobox.grid(row=3, column=2, pady=15,padx=10,sticky='E')
        tk.Button(self, text='收录', command=self.recode_info).grid(row=5, column=2, pady=10,sticky='E')
        tk.Button(self, text='修改', command=self.modify_info).grid(row=5, column=1, pady=10,sticky='W')
        
    def modify_info(self):
        try:
            s_sql = f'''select * from inventory where u_number={self.u_number.get()}'''
            s_data = db.search_data(s_sql)
            u_id = s_data[0][0]
            m_sql = f'''update inventory set U_type=?, U_number=?, U_status=? where id = {u_id}'''
            m_data = (self.u_type.get().strip(),self.u_number.get(),self.u_status.get())
            if self.u_type.get().strip() and self.u_number.get():
                db.insert_data(m_sql,m_data)
                messagebox.showinfo(title='信息', message='U盘修改成功！')
                self.u_type.set('')
                self.u_number.set('')
            else:
                messagebox.showwarning('警告', message='U盘类型不能为空！')
        except:
            messagebox.showwarning('警告', message='U盘编号不存在，请重新输入！')
        
    def recode_info(self):
        try:
            data = (self.u_type.get().strip(), self.u_number.get().strip(), self.u_status.get())
            sql = '''insert into inventory(U_type, U_number, U_status, use_status) values(?, ?, ?, '在库');'''
            if self.u_type.get().strip() and self.u_number.get().strip():
                db.insert_data(sql,data)
                messagebox.showinfo(title='信息', message='u盘登记成功！')
                self.u_type.set('')
                self.u_number.set('')
            else:
                if not self.u_type.get().strip():
                    messagebox.showwarning('警告', message='U盘类型不能为空！')
                elif not self.u_number.get().strip():
                    messagebox.showwarning('警告', message='U盘编号不能为空！')
        except:
            messagebox.showwarning(title='警告', message='U盘编号已存在！')


class TurnBackFrame(tk.Frame):
    """已还"""
    def __init__(self, root):
        super(TurnBackFrame, self).__init__(root)
        tk.Label(self, text='已还记录',font=("黑体",16)).pack()
        self.table_view = tk.Frame()
        self.table_view.pack()
        self.create_page()
    
    def create_page(self):
        col = ("id", "used", "U_type", "U_number", "used_date", "returner", "ret_date")
        self.tree_view = ttk.Treeview(self, 
                                      show='headings',
                                      height=8,
                                      columns=col)
        self.tree_view.column('id', width=30, anchor='center')
        self.tree_view.column('used', width=70, anchor='center')
        self.tree_view.column('U_type', width=60, anchor='center')
        self.tree_view.column('U_number', width=60, anchor='center')
        self.tree_view.column('used_date', width=80, anchor='center')
        self.tree_view.column('returner', width=70, anchor='center')
        self.tree_view.column('ret_date', width=80, anchor='center')
        self.tree_view.heading('id', text='id')
        self.tree_view.heading('used', text='借用人')
        self.tree_view.heading('U_type', text='U盘类型')
        self.tree_view.heading('U_number', text='U盘编号')
        self.tree_view.heading('used_date', text='借用时间')
        self.tree_view.heading('returner', text='归还人')
        self.tree_view.heading('ret_date', text='归还时间')
        self.tree_view.pack(fill=tk.BOTH, expand=True)
        tk.Button(self, text='刷新', command=self.show_data, font=("黑体",16)).pack(anchor=tk.E, pady=5)
        self.show_data()

    def show_data(self):
        s_sql = "SELECT id,used,U_type,U_number,use_date,returner,ret_date FROM rets;"
        try:
            use_infos = db.search_data(s_sql)
            obj = self.tree_view.get_children()
            if obj:
                for o in obj:
                    self.tree_view.delete(o)
            index = 0
            for i in use_infos:
                self.tree_view.insert('', index=index +1, values=i)
        except Exception as a:
            messagebox.showerror(title='错误', message=a)


class UnReturnedFrame(tk.Frame):
    """未还"""
    def __init__(self, root):
        super(UnReturnedFrame, self).__init__(root)
        tk.Label(self, text='未还记录',font=("黑体",16)).pack()
        self.table_view = tk.Frame()
        self.table_view.pack()
        self.create_page()

    def create_page(self):
        col = ("id", "used", "U_type", "U_number", "used_date")
        self.tree_view = ttk.Treeview(self, 
                                      show='headings',
                                      height=8,
                                      columns=col)
        self.tree_view.column('id', width=60, anchor='center')
        self.tree_view.column('used', width=100, anchor='center')
        self.tree_view.column('U_type', width=100, anchor='center')
        self.tree_view.column('U_number', width=60, anchor='center')
        self.tree_view.column('used_date', width=130, anchor='center')
        self.tree_view.heading('id', text='id')
        self.tree_view.heading('used', text='借用人')
        self.tree_view.heading('U_type', text='U盘类型')
        self.tree_view.heading('U_number', text='U盘编号')
        self.tree_view.heading('used_date', text='借用时间')
        self.tree_view.pack(fill=tk.BOTH, expand=True)
        tk.Button(self, text='刷新', command=self.show_data, font=("黑体",16)).pack(anchor=tk.E, pady=5)
        self.show_data()

    def show_data(self):
        s_sql = "SELECT id,used,U_type,U_number,use_date FROM lended;"
        try:
            use_infos = db.search_data(s_sql)
            obj = self.tree_view.get_children()
            if obj:
                for o in obj:
                    self.tree_view.delete(o)
            index = 0
            for i in use_infos:
                self.tree_view.insert('', index=index +1, values=i)
        except Exception as a:
            messagebox.showerror(title='错误', message=a)


class ReBackFrame(tk.Frame):
    """归还"""
    def __init__(self, root):
        super(ReBackFrame, self).__init__(root)
        tk.Label(self, text='U盘归还',font=("黑体",16)).grid(row=0,column=1,columnspan=2)
        self.u_num = tk.StringVar()
        self.returner = tk.StringVar()
        self.create_page()
        
    def create_page(self):
        tk.Label(self, text='U盘编号',font=("宋体",15)).grid(row=3, column=1, pady=20)
        tk.Entry(self, textvariable=self.u_num, width=17,font=("宋体",16)).grid(row=3, column=2, pady=10)
        tk.Label(self, text='归还者',font=("宋体",15)).grid(row=4, column=1, pady=20)
        tk.Entry(self, textvariable=self.returner, width=17,font=("宋体",16)).grid(row=4, column=2, pady=10)
        tk.Button(self, text='确认归还',font=("黑体",16), command=self.modify_db).grid(row=5, column=1, columnspan=2)
    
    def modify_db(self):
        tim = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
        try:
            unumber = self.u_num.get().strip()
            returner = self.returner.get().strip()
            le_sql = "select used,U_type,use_date from lended where U_number = {}".format(unumber)
            inven_sql = "update inventory set use_status= '在库' where U_number = ?;"
            r_sql = "INSERT INTO rets (used, U_number, U_type, returner, use_date, ret_date) VALUES (?, ?, ?, ?, ?, ?);"
            d_sql = "delete from lended where U_number = ?"
            out_data = db.search_data(le_sql)
            if out_data and returner:
                used,U_type,use_date = out_data[0]
                db.insert_data(inven_sql, (unumber,))
                db.insert_data(r_sql, (used, unumber, U_type, returner, use_date, tim))
                db.insert_data(d_sql, (unumber,))
                messagebox.showinfo(title='信息', message='归还成功，欢迎下次使用！')
                self.u_num.set('')
                self.returner.set('')
            else:
                if out_data == []:
                    messagebox.showwarning(title='警告', message='该编号的U盘未借出，请检查后重试！')
                else:
                    messagebox.showwarning(title='警告', message='请输入归还者名字！')
        except Exception as e:
            messagebox.showerror(title='错误', message='U盘编号不存在或未借出,请检查后再试!')


class LendoutFrame(tk.Frame):
    """借出"""
    def __init__(self, root):
        super(LendoutFrame, self).__init__(root)
        tk.Label(self, text='U盘借出',font=("黑体",16)).grid(row=0,column=1,columnspan=2)
        self.u_typer = tk.StringVar()
        self.u_numberd = tk.StringVar()
        self.used = tk.StringVar()
        self.create_page()
    
    def change_combbox(self,*args):
        n_sql = "select U_number from inventory where U_type ='{}' and U_status = '好' and use_status = '在库';".format(self.u_typer.get())
        u_numbers = ['请选择U盘编号']
        n_data = db.search_data(n_sql)
        for u_number, in n_data:
            if u_number not in u_numbers:
                u_numbers.append(u_number) 
        n_comb['value'] = u_numbers
        n_comb.current(0)
             
    def create_page(self):
        global n_comb
        t_data = db.search_data("select U_type from inventory;")
        u_types = ['请选择U盘类型']
        for u_type, in t_data:
            if u_type not in u_types:
                u_types.append(u_type)
        tk.Label(self, text='U盘类型',font=("宋体",15)).grid(row=1, column=1, pady=10,sticky='W')
        t_comb = tk.ttk.Combobox(self, textvariable=self.u_typer,width=15,height=5,font=("宋体",16),state="readonly")
        t_comb['value'] = u_types
        t_comb.current(0)
        t_comb.grid(row=1, column=2, pady=10,padx=10,sticky='E')
        
        tk.Label(self, text='U盘编号',font=("宋体",15)).grid(row=2, column=1, pady=10,sticky='W')
        n_comb = tk.ttk.Combobox(self, textvariable=self.u_numberd,width=15,height=10,font=("宋体",16),state="readonly")
        n_comb.grid(row=2, column=2, pady=10,padx=10,sticky='E')
        self.change_combbox()
        t_comb.bind("<<ComboboxSelected>>", self.change_combbox)
        
        tk.Label(self, text='使用者',font=("宋体",15)).grid(row=3, column=1, pady=20)
        tk.Entry(self, textvariable=self.used,width=17,font=("宋体",16)).grid(row=3, column=2, pady=10)
        tk.Button(self, text='借出',font=("黑体",16), command=self.recode_data).grid(row=5, column=1, columnspan=2)
    
    def recode_data(self):
        try:
            out_sql = "INSERT INTO lended (used, U_number, U_type, use_date) VALUES (?, ?, ?, ?);"
            inven_sql = "update inventory set use_status= '已借出' where U_number = ?;"
            tim = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
            if self.used.get().strip() and self.u_numberd.get() != '请选择U盘编号' and self.u_typer.get() != '请选择U盘类型':
                utype = ''
                unumber = ''
                utype = self.u_typer.get()
                unumber = self.u_numberd.get()
                data = (self.used.get().strip(),unumber,utype,tim)
                db.insert_data(out_sql, data)
                db.insert_data(inven_sql,(unumber,))
                messagebox.showinfo(title='Succeed', message='U盘借出成功!')
                self.u_numberd.set('请选择U盘编号')
                self.u_typer.set('请选择U盘类型')
                self.used.set('') 
            else:
                if self.u_typer.get() == '请选择U盘类型':
                    messagebox.showwarning(title='Warning!', message='请选择一个U盘类型！')
                elif self.u_numberd.get() == '请选择U盘编号' or self.u_numberd.get() == '':
                    messagebox.showwarning(title='Warning!', message='请选择一个U盘编号！')
                else:
                    messagebox.showwarning(title='Warning!', message='请输入U盘使用者姓名！')
        except Exception as e:
            messagebox.showerror(title='错误', message='只能选择一个编号！')
            self.u_numberd.set('请选择U盘编号')
            del utype, unumber
    